/**
 * 雪花键生成器
 */
CREATE SEQUENCE IF NOT EXISTS "public"."global_id_sequence";
CREATE OR REPLACE FUNCTION "public"."id_generator"(OUT result bigint) AS $$
DECLARE
	our_epoch bigint := 857736000000;
	seq_id bigint;
	now_millis bigint;
	shard_id int := 1;
BEGIN
	SELECT nextval('public.global_id_sequence') % 1024 INTO seq_id;

	SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
	result := (now_millis - our_epoch) << 23;
	result := result | (shard_id << 10);
	result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;

/**
 * 用户资料
 */
CREATE TABLE IF NOT EXISTS"user_profile"(
	"id"int8 DEFAULT"public"."id_generator"()PRIMARY KEY,
	"cellular"varchar,
	"user_id"varchar,
	"display_name"varchar,
	"language"varchar,
	"picture_url"text,
	"status_message"text,
	CHECK(
		CASE
			WHEN"cellular"IS NULL THEN"user_id"IS NOT NULL
			WHEN"user_id"IS NULL THEN"cellular"IS NOT NULL
		END
	)
);
COMMENT ON TABLE"user_profile"IS'用户资料';
COMMENT ON COLUMN"user_profile"."id"IS'主键';
COMMENT ON COLUMN"user_profile"."cellular"IS'手机号';
COMMENT ON COLUMN"user_profile"."user_id"IS'LINE user ID';
COMMENT ON COLUMN"user_profile"."display_name"IS'LINE user''s display name';
COMMENT ON COLUMN"user_profile"."language"IS'LINE user''s language';
COMMENT ON COLUMN"user_profile"."picture_url"IS'LINE user''s profile image URL';
COMMENT ON COLUMN"user_profile"."status_message"IS'LINE user''s status message';
CREATE UNIQUE INDEX ON"user_profile"("cellular");
CREATE UNIQUE INDEX ON"user_profile"("user_id");
